package com.itheima.jdbc;

import com.itheima.utils.JdbcUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 使用预处理对象，完成增删改查的操作
 *
 * 此处可以使用junit单元测试来编写 C   R    U     D 的测试方法
 *
 *
 */
public class JdbcDemo4 {

    //增加用户  username:小兰  password:admin  balance:100
    @Test
    public void testSave()throws Exception{
        //1.调用工具类获取连接
        Connection conn = JdbcUtil.getConnection();
        //2.获取预处理对象
        PreparedStatement pstm = conn.prepareStatement("insert into user(username,password,balance)values(?,?,?)");
        //3.给占位符赋值
        pstm.setString(1,"小兰");
        pstm.setString(2,"admin");
        pstm.setFloat(3,100l);
        //4.执行语句
        int res = pstm.executeUpdate();
        //5.获取执行结果
        System.out.println(res);
        //6.释放资源
        JdbcUtil.closeAll(null,pstm,conn);
    }



    //修改用户小兰的账户余额为1000
    @Test
    public void testUpdate()throws Exception {
        //1.用工具类获取连接
        Connection conn = JdbcUtil.getConnection();
        //2.获取预处理对象
        PreparedStatement pstm = conn.prepareStatement("update user set balance = ? where username = ? ");
        //3.给占位符赋值
        pstm.setFloat(1,1000f);
        pstm.setString(2,"小兰");
        //4.执行语句
        int res = pstm.executeUpdate();
        //5.获取执行结果
        System.out.println(res);
        //6.释放资源
        JdbcUtil.closeAll(null,pstm,conn);
    }


    //根据id查询
    @Test
    public void testFindById()throws Exception{
        //1.用工具类获取连接
        Connection conn = JdbcUtil.getConnection();
        //2.获取预处理对象
        PreparedStatement pstm = conn.prepareStatement("select * from user where id = ?");
        //3.给占位符赋值
        pstm.setInt(1,3);
        //4.执行语句
        ResultSet rs = pstm.executeQuery();
        //5.获取执行结果
        if (rs.next()){
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            float balance = rs.getFloat("balance");
            System.out.println(id+","+username+","+password+","+balance);
        }
        //6.释放资源
        JdbcUtil.closeAll(rs,pstm,conn);
    }


    //删除用户小兰
    @Test
    public void testDelete()throws Exception {
        //1.用工具类获取连接
        Connection conn = JdbcUtil.getConnection();
        //2.获取预处理对象
        PreparedStatement pstm = conn.prepareStatement("delete from user where id = ? ");
        //3.给占位符赋值
        pstm.setInt(1,3);
        //4.执行语句
        int res = pstm.executeUpdate();
        //5.获取执行结果
        System.out.println(res);
        //6.释放资源
        JdbcUtil.closeAll(null,pstm,conn);
    }



    //查询所有
    @Test
    public void testFindAll()throws Exception {
        //1.使用工具类获取连接
        Connection conn = JdbcUtil.getConnection();
        //2.获取预处理对象
        PreparedStatement pstm = conn.prepareStatement("select * from user");
        //3.执行语句
        ResultSet rs = pstm.executeQuery();
        //4.获取执行结果
        while (rs.next()){
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            float balance = rs.getFloat("balance");
            System.out.println(id+","+username+","+password+","+balance);
        }
        //5.释放资源
        JdbcUtil.closeAll(rs,pstm,conn);
    }



}
